import pymysql
from openpyxl import Workbook, load_workbook

# 创建数据库链接
conn = pymysql.Connect(
    host='192.168.200.123',
    port=3306,
    user='root',
    passwd="123456",
    db='learn_member',
    charset='utf8'
)


# 查询数据
def query_user():
    # 获取游标对象
    cur = conn.cursor()
    # 构建sql语句
    sql = 'select * from t_user'
    # 执行sql
    cur.execute(sql)
    # 保存结果
    res = cur.fetchall()
    return res


# 导入excel表格
def import_user(url):
    try:
        # 内存中创建一个workBook
        wb = Workbook()
        # 获取当前活跃的worksheet,默认取第一个
        ws = wb.active
        # 设置Excel头
        ws.title = '用户信息'
        # 设置第一行的标题
        ws.cell(row=1, column=1).value = 'id'
        ws.cell(row=1, column=2).value = 'account'
        ws.cell(row=1, column=3).value = 'description'
        ws.cell(row=1, column=4).value = 'password'
        ws.cell(row=1, column=5).value = 'name'
        # 获取数据
        ret = query_user()
        # 把数据添加进入表格
        for i in ret:
            ws.append(i)
        # 保存目录
        wb.save(url)
        wb.close()
    except Exception as e:
        print(e)


# 批量保存用户
def batch_save_user(row_number, rows, column_number):
    # 构建保存用户的sql语句
    add_sql = "insert into `learn_member`.`t_user`(`account`, `description`, `password`, `name`) " \
              "values (%s, %s, %s, %s)"
    # 需要保存的数据
    user_list = []
    for i in range(1, row_number):
        # 完整的一条数据添加
        users = []
        for j in range(1, column_number):
            # 获取i行j列的值
            data_value = rows[i][j].value
            users.append(data_value)
        user_list.append(users)
    try:
        # 获取游标对象
        cur = conn.cursor()
        # 执行sql语句
        count = cur.executemany(add_sql, user_list)
        # 提交
        conn.commit()
        print(f"成功插入数据{count}条")
    except Exception as e:
        print(e)
    finally:
        if cur:
            cur.close()
        if conn:
            conn.close()


def export_user(file_path):
    # 打开一个workBook
    wb = load_workbook(filename=file_path)
    # 获取所有表格sheet
    sheet_names = wb.sheetnames
    # 取第一个表格名称
    sheet_name = sheet_names[0]
    # 获取特点的worksheet对象
    ws = wb[sheet_name]
    # 表格的最大列
    column_number = ws.max_column
    # 表格最大的行
    row_number = ws.max_row
    # 获取表格所有的行和列
    rows = list(ws.rows)
    # columns = list(ws.columns)
    batch_save_user(row_number, rows, column_number)


# import_user("D://workspace//learn-demo//user.xls")
export_user("///user.xlsx")
